import os
import sys
import pymysql
import xlwt
import cv2
import numpy as np
import zipfile
import smtplib
import pickle
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from email.mime.application import MIMEApplication
from PyQt5.QtWidgets import *
from PyQt5.QtCore import Qt, QRegExp
from PyQt5.QtGui import QFont, QIcon, QRegExpValidator, QPalette, QPixmap

from admin.admin_dialogs import ClassData2Show, LessonData2Show
from gui_ui.admin_ui import Ui_AdminZone

class AdminControl(Ui_AdminZone, QWidget):
    def __init__(self):
        super(AdminControl, self).__init__()
        self.setupUi(self)
        self.initUI()

    def initUI(self):
        self.windowNatrue()
        self.center()
        self.signalSetting()
        self.classManageInit()
        self.lessonManageInit()
        self.signDataInit()

    def windowNatrue(self):
        self.setWindowTitle("智能考勤系统-管理员")
        self.setWindowIcon(QIcon("./gui_image/gui_Icon.png"))
        self.setToolTip("武昌工学院考勤系统")
        self.setWindowOpacity(0.95)
        pe = QPalette()
        self.setAutoFillBackground(True)
        pe.setColor(QPalette.Window, Qt.lightGray)
        self.setPalette(pe)

    def center(self):
        screen = QDesktopWidget().screenGeometry()
        size = self.geometry()
        newLeft = (screen.width() - size.width()) // 2
        newTop = (screen.height() - size.height()) // 2
        self.move(newLeft, newTop)

    def onTreeClicked(self):
        item = self.treeWidget.currentItem()
        index_top = self.treeWidget.indexOfTopLevelItem(item)
        self.stackedWidget.setCurrentIndex(index_top)

    def closeEvent(self, event):
        closeresult = QMessageBox.question(self, "关闭提示：", "您真的要退出吗？", 
                                         QMessageBox.Yes | QMessageBox.No,
                                         QMessageBox.No)
        if closeresult == QMessageBox.Yes:
            event.accept()
        else:
            event.ignore()

    def signalSetting(self):
        self.treeWidget.clicked.connect(self.onTreeClicked)
        
        # 班级管理页面信号
        self.addClassButton.clicked.connect(self.addClassFunc)
        self.freshClassButton.clicked.connect(self.classManageInit)
        self.saveClassButton.clicked.connect(self.saveClassFunc)
        self.classData2show.clicked.connect(self.showClassData)
        
        # 课程管理页面信号
        self.addLessonButton.clicked.connect(self.addLessonFunc)
        self.freshLessonButton.clicked.connect(self.lessonManageInit)
        self.saveLessonButton.clicked.connect(self.saveLessonFunc)
        self.lessonData2show.clicked.connect(self.showLessonData)
        
        # 签到数据页面信号
        self.freshSignData.clicked.connect(self.signDataInit)
        self.save2Excel.clicked.connect(self.save2Excel)
        self.send2email.clicked.connect(self.send2email_Func)
        self.addEmailAccount.clicked.connect(self.addEmailAccountFunc)

    # 班级管理页面初始化
    def classManageInit(self):
        self.classNumberInit()
        self.classNameInit()
        self.classSumInit()
        self.classIdTipInit()
        self.signTimeInit()

    def classNumberInit(self):
        classNumber_val = QRegExpValidator(QRegExp("^[0-9]{8}$"))
        self.classNumber.setValidator(classNumber_val)
        self.classNumber.setPlaceholderText("请输入8位班级编号")

    def classNameInit(self):
        className_val = QRegExpValidator(QRegExp("[\u4e00-\u9fa5]{1,10}$"))
        self.className.setValidator(className_val)
        self.className.setPlaceholderText("请输入班级名称")

    def classSumInit(self):
        classSum_val = QRegExpValidator(QRegExp("^[0-9]{1,3}$"))
        self.classSum.setValidator(classSum_val)
        self.classSum.setPlaceholderText("请输入班级人数")

    def classIdTipInit(self):
        self.idTip.addItem("Id-Num")
        self.idTip.addItem("Id")

    def signTimeInit(self):
        self.signStartTime.setTime(self.signStartTime.time())
        self.signEndTime.setTime(self.signEndTime.time())

    def addClassFunc(self):
        classNumber_tempor = self.classNumber.text()
        className_tempor = self.className.text()
        classSum_tempor = self.classSum.text()
        idTip_tempor = self.idTip.currentText()
        signStartTime_tempor = self.signStartTime.time().toString("hh:mm")
        signEndTime_tempor = self.signEndTime.time().toString("hh:mm")

        if not (classNumber_tempor and className_tempor and classSum_tempor):
            QMessageBox.warning(self, "警告", "请填写完整信息！")
            return

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = 'select * from classtable where majorNumber = %s'
        rows = cursor.execute(sql, classNumber_tempor)

        if rows:
            QMessageBox.warning(self, "警告", "该班级已存在！")
        else:
            sql_insert = 'insert into classtable (majorNumber, majorName, classNumber, idTip, signStartTime, signEndTime) values(%s, %s, %s, %s, %s, %s)'
            cursor.execute(sql_insert, (classNumber_tempor, className_tempor, classSum_tempor, idTip_tempor, signStartTime_tempor, signEndTime_tempor))
            conn.commit()
            QMessageBox.about(self, "提示", "添加成功！")

        cursor.close()
        conn.close()

    def saveClassFunc(self):
        classNumber_tempor = self.classNumber.text()
        className_tempor = self.className.text()
        classSum_tempor = self.classSum.text()
        idTip_tempor = self.idTip.currentText()
        signStartTime_tempor = self.signStartTime.time().toString("hh:mm")
        signEndTime_tempor = self.signEndTime.time().toString("hh:mm")

        if not classNumber_tempor:
            QMessageBox.warning(self, "警告", "请输入班级编号！")
            return

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = 'select * from classtable where majorNumber = %s'
        rows = cursor.execute(sql, classNumber_tempor)

        if rows:
            sql_update = 'update classtable set majorName=%s, classNumber=%s, idTip=%s, signStartTime=%s, signEndTime=%s where majorNumber=%s'
            cursor.execute(sql_update, (className_tempor, classSum_tempor, idTip_tempor, signStartTime_tempor, signEndTime_tempor, classNumber_tempor))
            conn.commit()
            QMessageBox.about(self, "提示", "修改成功！")
        else:
            QMessageBox.warning(self, "警告", "该班级不存在！")

        cursor.close()
        conn.close()

    def showClassData(self):
        class_data_window = ClassData2Show()
        class_data_window.show()

    # 课程管理页面初始化
    def lessonManageInit(self):
        self.lessonClassNumberInit()
        self.lessonNameInit()
        self.lessonDateInit()
        self.lessonWeekInit()
        self.lessonTimeInit()

    def lessonClassNumberInit(self):
        lessonClassNumber_val = QRegExpValidator(QRegExp("^[0-9]{8}$"))
        self.lessonClassNumber.setValidator(lessonClassNumber_val)
        self.lessonClassNumber.setPlaceholderText("请输入8位班级编号")

    def lessonNameInit(self):
        self.lessonName.setPlaceholderText("请输入课程名称")

    def lessonDateInit(self):
        self.lessonStartDate.setDate(self.lessonStartDate.date())
        self.lessonEndDate.setDate(self.lessonEndDate.date())

    def lessonWeekInit(self):
        week_days = ["周一", "周二", "周三", "周四", "周五", "周六", "周日"]
        self.lessonWeek.addItems(week_days)

    def lessonTimeInit(self):
        self.lessonStartTime.setTime(self.lessonStartTime.time())
        self.lessonEndTime.setTime(self.lessonEndTime.time())

    def addLessonFunc(self):
        lessonClassNumber_tempor = self.lessonClassNumber.text()
        lessonName_tempor = self.lessonName.text()
        lessonStartDate_tempor = self.lessonStartDate.date().toString("yyyy-MM-dd")
        lessonEndDate_tempor = self.lessonEndDate.date().toString("yyyy-MM-dd")
        lessonWeek_tempor = self.lessonWeek.currentText()
        lessonStartTime_tempor = self.lessonStartTime.time().toString("hh:mm")
        lessonEndTime_tempor = self.lessonEndTime.time().toString("hh:mm")

        if not (lessonClassNumber_tempor and lessonName_tempor):
            QMessageBox.warning(self, "警告", "请填写完整信息！")
            return

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = 'select * from lessontable where toClass = %s and lessonName = %s'
        rows = cursor.execute(sql, (lessonClassNumber_tempor, lessonName_tempor))

        if rows:
            QMessageBox.warning(self, "警告", "该课程已存在！")
        else:
            sql_insert = 'insert into lessontable (toClass, lessonName, startDate, endDate, weekDay, startTime, endTime) values(%s, %s, %s, %s, %s, %s, %s)'
            cursor.execute(sql_insert, (lessonClassNumber_tempor, lessonName_tempor, lessonStartDate_tempor, lessonEndDate_tempor, lessonWeek_tempor, lessonStartTime_tempor, lessonEndTime_tempor))
            conn.commit()
            QMessageBox.about(self, "提示", "添加成功！")

        cursor.close()
        conn.close()

    def saveLessonFunc(self):
        lessonClassNumber_tempor = self.lessonClassNumber.text()
        lessonName_tempor = self.lessonName.text()
        lessonStartDate_tempor = self.lessonStartDate.date().toString("yyyy-MM-dd")
        lessonEndDate_tempor = self.lessonEndDate.date().toString("yyyy-MM-dd")
        lessonWeek_tempor = self.lessonWeek.currentText()
        lessonStartTime_tempor = self.lessonStartTime.time().toString("hh:mm")
        lessonEndTime_tempor = self.lessonEndTime.time().toString("hh:mm")

        if not (lessonClassNumber_tempor and lessonName_tempor):
            QMessageBox.warning(self, "警告", "请填写完整信息！")
            return

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )
        
        cursor = conn.cursor()

        sql = 'select * from lessontable where toClass = %s and lessonName = %s'
        rows = cursor.execute(sql, (lessonClassNumber_tempor, lessonName_tempor))

        if rows:
            sql_update = 'update lessontable set startDate=%s, endDate=%s, weekDay=%s, startTime=%s, endTime=%s where toClass=%s and lessonName=%s'
            cursor.execute(sql_update, (lessonStartDate_tempor, lessonEndDate_tempor, lessonWeek_tempor, lessonStartTime_tempor, lessonEndTime_tempor, lessonClassNumber_tempor, lessonName_tempor))
            conn.commit()
            QMessageBox.about(self, "提示", "修改成功！")
        else:
            QMessageBox.warning(self, "警告", "该课程不存在！")

        cursor.close()
        conn.close()

    def showLessonData(self):
        lesson_data_window = LessonData2Show()
        lesson_data_window.show()

    # 签到数据页面初始化
    def signDataInit(self):
        self.signClassNumberInit()
        self.signDateInit()

    def signClassNumberInit(self):
        signClassNumber_val = QRegExpValidator(QRegExp("^[0-9]{8}$"))
        self.classNum2see.setValidator(signClassNumber_val)
        self.classNum2see.setPlaceholderText("请输入8位班级编号")

    def signDateInit(self):
        self.date2see.setDate(self.date2see.date())

    def seeSignData(self):
        classNum2see_tempor = self.classNum2see.text()
        date2see_tempor = self.date2see.date().toString("yyyy-MM-dd")

        if not classNum2see_tempor:
            QMessageBox.warning(self, "警告", "请输入班级编号！")
            return

        signUpSheetName = str(classNum2see_tempor) + "_signdata"

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )

        cursor = conn.cursor()

        sql = 'select * from information_schema.tables where table_name = %s'
        rows = cursor.execute(sql, signUpSheetName)
        
        if not rows:
            QMessageBox.about(self, '提示', "该班级的签到表单不存在！")
            return

        conn.commit()
        cursor.close()
        conn.close()

        successSignState = '1'
        lateSignState = '2'
        freeSignState = '3'

        # 刷新签到记录表格
        self.refreshSignDataTable(signUpSheetName, date2see_tempor, successSignState, self.successSignData_Table)
        # 刷新迟到记录表格
        self.refreshSignDataTable(signUpSheetName, date2see_tempor, lateSignState, self.lateSignData_Table)
        # 刷新请假记录表格
        self.refreshSignDataTable(signUpSheetName, date2see_tempor, freeSignState, self.freeSignData_Table)

    def refreshSignDataTable(self, signUpSheetName, date, state, table_widget):
        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )

        cursor = conn.cursor()

        sql = "select * from %s where signState = %s and signDate = %s" % (signUpSheetName, state, '%s')
        cursor.execute(sql, date)

        rows = cursor.fetchall()
        rowsum = cursor.rowcount

        if rowsum == 0:
            cursor.close()
            conn.close()
            table_widget.setRowCount(0)
            return

        vol = 5

        cursor.close()
        conn.close()

        # 处理数据并填充表格
        user_data = []
        for i in range(rowsum):
            user_id = str(rows[i][0])[8:10]  # 取学号后2位
            user_name = str(rows[i][1])
            lesson_name = str(rows[i][2])
            location = str(rows[i][3])
            sign_date = str(rows[i][4])
            sign_time = str(rows[i][5])
            
            user_data.append([user_id, user_name, location, sign_date, sign_time])

        table_widget.setRowCount(rowsum)
        table_widget.setColumnCount(vol)

        headers = ['学号', '姓名', '签到地点', '签到日期', '签到时间']
        table_widget.setHorizontalHeaderLabels(headers)

        table_widget.setEditTriggers(QAbstractItemView.NoEditTriggers)
        table_widget.setSelectionBehavior(QAbstractItemView.SelectRows)
        table_widget.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
        table_widget.verticalHeader().setSectionResizeMode(QHeaderView.Stretch)

        for i in range(rowsum):
            for j in range(vol):
                data = QTableWidgetItem(str(user_data[i][j]))
                table_widget.setItem(i, j, data)

    def save2Excel(self):
        classNum2see_tempor = self.classNum2see.text()
        signUpSheetName = str(classNum2see_tempor) + "_signdata"
        save2path = './save2Excel/' + signUpSheetName + '.xls'

        conn = pymysql.connect(
            host='47.93.16.211',
            port=3306,
            user='root',
            password='123456',
            db='mytest',
            charset='utf8'
        )

        cursor = conn.cursor()

        sql = 'select * from information_schema.tables where table_name = %s'
        rows = cursor.execute(sql, signUpSheetName)
        
        if not rows:
            QMessageBox.about(self, '提示', "该班级的签到表单不存在！")
            return

        sql = 'select * from %s' % signUpSheetName
        cursor.execute(sql)

        results = cursor.fetchall()
        fields = cursor.description

        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet(signUpSheetName, cell_overwrite_ok=True)

        for field in range(0, len(fields)):
            sheet.write(0, field, fields[field][0])

        for row in range(1, len(results) + 1):
            for col in range(0, len(fields)):
                sheet.write(row, col, u'%s' % results[row - 1][col])

        workbook.save(save2path)
        QMessageBox.about(self, '提示', "导出成功！")

        cursor.close()
        conn.close()

    def writeAllFileToZip(self, absDir, zipFile):
        for f in os.listdir(absDir):
            absFile = os.path.join(absDir, f)
            if os.path.isdir(absFile):
                relFile = absFile[len(os.getcwd()) + 1:]
                zipFile.write(relFile)
                self.writeAllFileToZip(absFile, zipFile)
            else:
                relFile = absFile[len(os.getcwd()) + 1:]
                zipFile.write(relFile)
        return

    def fileSaveZip2Path(self):
        zipFilePath = os.path.join(sys.path[0], "ExcelData.zip")
        zipFile = zipfile.ZipFile(zipFilePath, "w", zipfile.ZIP_DEFLATED)
        absDir = os.path.join(sys.path[0], "save2Excel")
        self.writeAllFileToZip(absDir, zipFile)

    def send2email_Func(self):
        self.fileSaveZip2Path()
        zipFilePath = os.path.join(sys.path[0], "ExcelData.zip")
        txtpath = './autoLoginFile/QMailSMTP.txt'
        picklepath = './autoLoginFile/QMailSMTP.pkl'
        
        infFromTxt = []
        if os.path.exists(txtpath):
            with open('./autoLoginFile/QMailSMTP.txt', 'r') as f:
                for line in f.readlines():
                    infFromTxt_tempor = line.strip('\n')
                    infFromTxt.append(infFromTxt_tempor)

            with open('./autoLoginFile/QMailSMTP.pkl', 'wb') as f:
                pickle.dump(infFromTxt, f)

        if not os.path.exists(picklepath):
            QMessageBox.warning(self, '提示', '您未添加开启了STMP服务的邮箱账号和授权码！')
            return

        with open('./autoLoginFile/QMailSMTP.pkl', 'rb') as f:
            infFromPkl = pickle.load(f)

        to_mails = self.excel2Email.text()
        from_mail = str(infFromPkl[0])
        mail_pass = str(infFromPkl[1])

        msg = MIMEText("""尊敬的管理员，你好：
            这是今日份的Excel报表！
            请查收！
        祝好.
                  阿汪先生
            """, "plain", "utf-8")
        content_part = msg

        zipFile = zipFilePath
        zip = MIMEApplication(open(zipFile, 'rb').read())
        zip.add_header('Content-Disposition', 'attachment', filename='ExcelData.zip')

        m = MIMEMultipart()
        m.attach(content_part)
        m.attach(zip)

        m['Subject'] = '今日份Excel表单'
        m['From'] = from_mail
        m['To'] = to_mails

        try:
            server = smtplib.SMTP('smtp.qq.com')
            server.login(from_mail, mail_pass)
            server.sendmail(from_mail, to_mails.split(','), m.as_string())
            QMessageBox.about(self, '提示', '发送成功！')
            server.quit()
        except smtplib.SMTPException as e:
            print('error:', e)
            QMessageBox.warning(self, '提示', '发送失败！')

    def addEmailAccountFunc(self):
        email_dialog = QDialog(self)
        email_dialog.setWindowTitle("添加邮箱账号")
        email_dialog.setFixedSize(300, 200)

        layout = QVBoxLayout()

        email_label = QLabel("邮箱地址:")
        self.email_input = QLineEdit()
        self.email_input.setPlaceholderText("请输入QQ邮箱地址")

        password_label = QLabel("授权码:")
        self.password_input = QLineEdit()
        self.password_input.setEchoMode(QLineEdit.Password)
        self.password_input.setPlaceholderText("请输入SMTP授权码")

        button_layout = QHBoxLayout()
        save_button = QPushButton("保存")
        cancel_button = QPushButton("取消")

        save_button.clicked.connect(lambda: self.saveEmailAccount(email_dialog))
        cancel_button.clicked.connect(email_dialog.close)

        button_layout.addWidget(save_button)
        button_layout.addWidget(cancel_button)

        layout.addWidget(email_label)
        layout.addWidget(self.email_input)
        layout.addWidget(password_label)
        layout.addWidget(self.password_input)
        layout.addLayout(button_layout)

        email_dialog.setLayout(layout)
        email_dialog.exec_()

    def saveEmailAccount(self, dialog):
        email = self.email_input.text()
        password = self.password_input.text()

        if not email or not password:
            QMessageBox.warning(self, "警告", "请填写完整的邮箱和授权码信息！")
            return

        # 保存到文件
        with open('./autoLoginFile/QMailSMTP.txt', 'w') as f:
            f.write(email + '\n')
            f.write(password + '\n')

        QMessageBox.about(self, "提示", "邮箱账号保存成功！")
        dialog.close()